<?php

/* vim: set expandtab tabstop=4 shiftwidth=4 softtabstop=4: */

/**
 * This file contains the following classes:
 *
 * - DB
 * - DB_Statement
 * - DB_Exception
 * - DB_ConnectException
 * - DB_SelectDatabaseException
 * - DB_QueryException
 * - DB_MismatchException
 * - DB_FileAccessException
 * - DB_InvalidLockException
 *
 * PHP version 5
 *
 * This file is part of Majic - A rapid-development web application framework
 * for PHP 5.
 *
 * Majic is free software: you can redistribute it and/or modify it under
 * the terms of the GNU Lesser General Public License as published by the Free
 * Software Foundation, either version 3 of the License, or (at your option)
 * any later version.
 *
 * Majic is distributed in the hope that it will be useful, but WITHOUT ANY
 * WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
 * FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more
 * details.
 *
 * You should have received a copy of the GNU Lesser General Public License
 * along with Majic. If not, see <http://www.gnu.org/licenses/>.
 *
 * @package     Majic
 * @author      Michael J. I. Jackson <mjijackson@gmail.com>
 * @copyright   2007-2008 Michael J. I. Jackson
 * @license     http://www.gnu.org/licenses/lgpl-3.0.txt GNU LGPL 3.0
 * @version     SVN: $Id: DB.php 36 2008-08-09 23:31:49Z mjijackson $
 */

/**
 * A database exception class built for MySQL 5. Supports emulated prepared
 * statements and multiple connection handling.
 *
 * @package     DB
 * @author      Michael J. I. Jackson <mjijackson@gmail.com>
 */
class DB
{

    /**
     * The name of the database for this connection.
     *
     * @var     string
     * @access  protected
     */
    protected $db;

    /**
     * The hostname for this connection.
     *
     * @var     string
     * @access  protected
     */
    protected $host;

    /**
     * The user name for this connection.
     *
     * @var     string
     * @access  protected
     */
    protected $user;

    /**
     * The password for this connection.
     *
     * @var     string
     * @access  protected
     */
    protected $pass;

    /**
     * Contains the options to use for this connection.
     *
     * @var     array
     * @access  private
     */
    private $options = array(
        'persistent'        => true,    // use persistent connections
        'count_delete'      => true,    // count the number of rows deleted on DELETE statements
        'auto_free'         => true,    // automatically free exhausted result sets
        'quote_identifiers' => true     // automatically quote identifier names in select, insert, update, and delete methods
    );

    /**
     * Contains debugging information associated with this connection.
     *
     * @var     array
     * @access  private
     */
    private $debug_info = array();

    /**
     * This connection's database connection handle.
     *
     * @var     resource
     * @access  private
     */
    private $handle;

    /**
     * This connection's database result set.
     *
     * @var     resource
     * @access  private
     */
    private $result;

    /**
     * Connects to the database using the provided parameters. Note: These
     * may be supplied later in connect().
     *
     * @param   string  $host   The database server address
     * @param   string  $user   The user name
     * @param   string  $pass   The user's password
     * @param   string  $db     The name of the database to use
     * @return  void
     * @access  private
     */
    public function __construct($host = '', $user = '', $pass = '', $db = null)
    {
        $this->setParams($host, $user, $pass, $db);
    }

    /**
     * Sets this connection's database connection parameters.
     *
     * @param   string  $host   The database server address
     * @param   string  $user   The user name
     * @param   string  $pass   The user's password
     * @param   string  $db     The name of the database to use
     * @return  bool            True if any of the connection parameters were
     *                          changed, false otherwise
     * @access  private
     */
    private function setParams($host, $user, $pass, $db)
    {
        $params = array('host', 'user', 'pass', 'db');
        foreach ($params as $param) {
            if ($$param) {
                $this->$param = $$param;
            }
        }
    }

    /**
     * Connects to the database using the provided parameters. If a
     * connection has already been established and no new parameters are
     * supplied, will re-use the existing connection. However, if new
     * connection parameters are supplied, the old connection will be closed
     * first. Note: These parameters do not need to be supplied here at all if
     * already given in __construct() or hard coded into a subclass.
     *
     * @param   string  $host   The database server address
     * @param   string  $user   The user name
     * @param   string  $pass   The user's password
     * @param   string  $db     The name of the database to use
     * @return  int             The database link identifier
     * @throws  DB_ConnectException         If there is an error connecting to
     *                                      the database
     * @throws  DB_SelectDatabaseException  If there is an error selecting the
     *                                      given database
     * @access  public
     */
    public function connect($host = '', $user = '', $pass = '', $db = null)
    {
        $this->setParams($host, $user, $pass, $db);

        if ($this->options['persistent']) {
            // use persistent connection
            $this->handle = @mysql_pconnect($this->host, $this->user, $this->pass);
        } else {
            $this->handle = @mysql_connect($this->host, $this->user, $this->pass);
        }
        if (!$this->handle) {
            throw new DB_ConnectException($this->host, $this->user, $this->pass);
        }

        if ($this->db) {
            $this->selectDatabase($this->db);
        }

        return $this->handle;
    }

    /**
     * Selects the given database for use on subsequent queries.
     *
     * @param   string  $db     The database name
     * @return  void
     * @throws  DB_SelectDatabaseException  If there is an error selecting the
     *                                      given database
     * @access  public
     */
    public function selectDatabase($db)
    {
        if (@mysql_select_db($db) === false) {
            throw new DB_SelectDatabaseException($db);
        }
    }

    /**
     * Closes this connection to the database. Note: This function will always
     * return false when using persistent connections because they cannot be
     * closed using mysql_close().
     *
     * @return  bool        True on success, false on failure
     * @access  public
     */
    public function disconnect()
    {
        $ret = @mysql_close($this->handle);
        $this->handle = null;
        return $ret;
    }

    /**
     * Executes the given query on the database. Automatically frees the old
     * result set (if it exists).
     *
     * @param   string      $query      The query to execute
     * @return  mixed                   The result set of the query (resource)
     *                                  for SELECT type statements, a boolean
     *                                  indicating success/failure for
     *                                  manipulative queries
     * @throws  DB_QueryException       If query execution fails
     * @access  private
     */
    private function doQuery($query)
    {
        if (!$this->handle) {
            $this->connect();
        }
        if ($this->result) {
            $this->free(); // automatically free old result
        }

        $this->debug("Executing SQL: $query");
        $this->result = @mysql_query($query, $this->handle); // execute the query

        if (!$this->result) {
            throw new DB_QueryException($query, $this->errorCode(), $this->error());
        }

        return $this->result;
    }

    /**
     * Executes the given query on the database. If a parameter(s) is
     * supplied here, the query will automatically be prepared using prepare()
     * and executed using execute(). Implicitly throws DB_QueryException if the
     * query fails.
     *
     * @param   string      $query      The query to execute
     * @param   mixed       $params     (optional) The parameters to use in a
     *                                  prepared statement
     * @return  resource                The result of the query
     * @access  public
     */
    public function query($query, $params = array())
    {
        if (empty($params)) {
            return $this->doQuery($query);
        } else {
            $stmt = $this->prepare($query);
            return $stmt->execute($params);
        }
    }

    /**
     * Executes the given manipulative query on the database. Note: Manipulative
     * queries are those that modify rows in the database (e.g. UPDATE, INSERT,
     * DELETE, etc.). If a parameter(s) is supplied here, the query will
     * automatically be prepared using prepare() and executed using execute().
     * Implicitly throws DB_QueryException if the query fails.
     *
     * @param   string      $query      The query to execute
     * @param   mixed       $params     (optional) The parameters to use in a
     *                                  prepared statement
     * @return  int                     The number of affected rows
     * @access  public
     */
    public function exec($query, $params = array())
    {
        if (empty($params)) {
            // In MySQL, "DELETE FROM tbl" gives 0 affected rows. This little hack
            // will return the number of deleted rows (if desired).
            if ($this->options['count_delete']
                && preg_match('/^\s*DELETE\s+FROM\s+(\S+)\s*$/i', $query)) {
                    $query = preg_replace('/^\s*DELETE\s+FROM\s+(\S+)\s*$/',
                        'DELETE FROM \1 WHERE 1=1', $query);
            }

            $this->doQuery($query);
        } else {
            $stmt = $this->prepare($query);
            $stmt->execute($params);
        }

        return $this->affectedRows();
    }

    /**
     * Creates a prepared statement for multiple execution using the given
     * query and this connection database handle.
     *
     * @param   string      $query      The query to prepare
     * @return  DB_Statement            The new prepared statement object
     * @access  public
     * @see     DB_Statement::__construct()
     */
    public function prepare($query)
    {
        if (!isset($this->stmts[$query])) {
            $this->stmts[$query] = new DB_Statement($query, $this);
        }

        return $this->stmts[$query];
    }

    /**
     * Executes the given prepared statement using the given parameter(s) and
     * this database connection handle. Implicitly throws exceptions.
     *
     * @param   DB_Statement    $stmt       The prepared statement
     * @param   mixed           $params     The parameters to use
     * @return  mixed                       The number of affected rows if the
     *                                      query is {@link DB::isManip() manipulative},
     *                                      the query result otherwise
     * @access  public
     * @see     DB_Statement::execute()
     */
    public function execute(DB_Statement $stmt, $params = array())
    {
        return $stmt->execute($params, $this);
    }

    /**
     * Executes the given query on the database, frees the result, and returns
     * the value from the given column of the first result. If the column is
     * numeric, will return the value of that column number in the order of the
     * result set. Defaults to the first column.
     *
     * NOTE: Will return NULL if no results are found.
     *
     * @param   string      $query      The query to execute
     * @param   mixed       $params     (optional) The parameters to use in a
     *                                  prepared statement
     * @param   mixed       $col        (optional) The column to return
     * @return  mixed                   The value of the column
     * @access  public
     */
    public function queryOne($query, $params = array(), $col = 0)
    {
        $this->query($query, $params);
        $row = $this->fetchRow(MYSQL_BOTH);
        $this->free();

        return $row ? $row[$col] : null;
    }

    /**
     * Executes the given query on the database, frees the result, and returns
     * the values from the given column of each row in the result set in an
     * array.
     *
     * NOTE: Will return an empty array if no results are found.
     *
     * @param   string      $query      The query to execute
     * @param   mixed       $params     (optional) The parameters to use in a
     *                                  prepared statement
     * @param   mixed       $col        (optional) The column to return
     * @return  array                   The values from the given column from
     *                                  all results
     * @access  public
     */
    public function queryCol($query, $params = array(), $col = 0)
    {
        $this->query($query, $params);
        $column = array();
        while ($row = $this->fetchRow(MYSQL_BOTH)) {
            $column[] = $row[$col];
        }
        $this->free();

        return $column;
    }

    /**
     * Executes the given query on the database, frees the result, and returns
     * the first result as an array. The type of array to be returned is
     * determined by the $mode parameter. Defaults to an associative array.
     *
     * NOTE: Will return NULL if no results are found.
     *
     * @param   string      $query      The query to execute
     * @param   mixed       $params     (optional) The parameters to use in a
     *                                  prepared statement
     * @param   int         $mode       (optional) The fetch mode to use, one of
     *                                  MYSQL_ASSOC, MYSQL_NUM, or MYSQL_BOTH
     * @return  array                   The first result as an associative array
     * @access  public
     */
    public function queryRow($query, $params = array(), $mode = MYSQL_ASSOC)
    {
        $this->query($query, $params);
        $row = $this->fetchRow($mode);
        $this->free();

        return $row ? $row : null;
    }

    /**
     * Executes the given query on the database, frees the result, and returns
     * the first result as an object. The type of object to be returned is
     * determined by the $cls parameter. Defaults to stdClass.
     *
     * NOTE: Will return NULL if no results are found.
     *
     * @param   string      $query      The query to execute
     * @param   mixed       $params     (optional) The parameters to use in a
     *                                  prepared statement
     * @param   string      $cls        (optional) The name of the class to
     *                                  create instances of
     * @param   array       $args       (optional) Arguments to pass to the
     *                                  class constructor
     * @return  object                  The first result as an object
     * @access  public
     */
    public function queryObj($query, $params = array(), $cls = 'stdClass',
        $args = array())
    {
        $this->query($query, $params);
        $obj = $this->fetchObj($cls, $args);
        $this->free();

        return $obj ? $obj : null;
    }

    /**
     * Executes the given query on the database, frees the result, and returns
     * the entire result set in an array. The type of elements the array will
     * contain is determined by the $mode parameter. If this parameter is one of
     * MYSQL_ASSOC, MYSQL_NUM, or MYSQL_BOTH, the elements will be arrays of
     * the specified type. Otherwise, $mode is assumed to be the class name of
     * the objects to be instantiated. Defaults to MYSQL_ASSOC.
     *
     * NOTE: Will return an empty array if no results are found.
     *
     * <code>
     * $all = $db->queryAll('SELECT * FROM tbl', null, MYSQL_NUM);
     * $all = $db->queryAll('SELECT * FROM ?', $table, 'stdClass');
     * </code>
     *
     * @param   string      $query      The query to execute
     * @param   mixed       $params     (optional) The parameters to use in a
     *                                  prepared statement
     * @param   mixed       $mode       (optional) The fetch mode to use, one of
     *                                  MYSQL_ASSOC, MYSQL_NUM, MYSQL_BOTH, or,
     *                                  in the case of objects, the class name
     * @param   array       $args       (optional) Some arguments to pass to the
     *                                  class constructor when generating
     *                                  objects
     * @return  array                   The entire result set in the desired
     *                                  format
     * @access  public
     */
    public function queryAll($query, $params = array(), $mode = MYSQL_ASSOC,
        $args = array())
    {
        $this->query($query, $params);

        $all = array();
        if (is_int($mode)) {
            while ($row = $this->fetchRow($mode)) {
                $all[] = $row;
            }
        } else {
            while ($obj = $this->fetchObj($mode, $args)) {
                $all[] = $obj;
            }
        }

        $this->free();

        return $all;
    }

    /**
     * Locks the specified tables in the database. Possible locking modes are:
     *
     * READ                 All processes may only read from the table(s).
     * READ LOCAL           Allows non-conflicting INSERT calls from local
     *                      processes.
     * WRITE                Disallows all other processes from writing (or
     *                      reading from) the table(s).
     * LOW_PRIORITY WRITE   Allows subsequent READ lock requests by other
     *                      processes to be satisfied first if they occur while
     *                      the the LOW_PRIORITY WRITE request is waiting.
     *
     * IMPORTANT: It is your responsibility to unlock any tables that you lock
     * when you are finished using them!
     *
     * To lock just one table, pass in the table name and the locking mode.
     *
     * <code>
     * $db->lock('tbl', 'LOW_PRIORITY WRITE');
     * </code>
     *
     * To lock multiple tables with the same locking mode, pass in an array of
     * table names as the first parameter, followed by the locking mode.
     *
     * <code>
     * $db->lock(array('tbl1', 'tbl2'), 'READ LOCAL');
     * </code>
     *
     * To lock multiple tables with different locking modes, pass in an
     * associative array with the table names as keys and the respective locking
     * mode as that key's value.
     *
     * <code>
     * $db->lock(array(
     *     'tbl1' => 'READ',
     *     'tbl2' => 'WRITE'
     * ));
     * </code>
     *
     * Mode defaults to WRITE. See http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html
     * for more information.
     *
     * @param   mixed       $tables         The table(s) to lock
     * @param   string      $mode           The mode to use when locking
     * @return  int                         The number of affected rows (should
     *                                      always be 0)
     * @throws  DB_InvalidLockException     If an invalid lock type is used
     * @access  public
     */
    public function lock($tables, $mode = 'WRITE')
    {
        $tables = (array) $tables;

        static $valid_locks = array('WRITE', 'READ', 'READ LOCAL',
            'LOW_PRIORITY WRITE');

        $locks = array();
        foreach ($tables as $key => $value) {
            if (is_int($key)) {
                $tbl = $value;
                $lock = strtoupper($mode);
            } else {
                $tbl = $key;
                $lock = strtoupper($value);
            }
            if (!in_array($lock, $valid_locks)) {
                throw new DB_InvalidLockException($lock);
            }

            $locks[] = $tbl . ' ' . $lock;
        }

        return $this->exec('LOCK TABLES ' . implode(', ', $locks));
    }

    /**
     * Unlocks all tables that are currently locked by this connection.
     *
     * @return  int                         The number of affected rows (should
     *                                      always be 0)
     * @access  public
     */
    public function unlock()
    {
        return $this->exec('UNLOCK TABLES');
    }

    /**
     * Surrounds the given value in quotes (if necessary) and escapes it for
     * use in an SQL statement.
     *
     * <code>
     * $sql = "INSERT INTO tbl (name) VALUES (" . $db->quote($_GET['name']) . ")";
     * $affected_rows = $db->exec($sql);
     * </code>
     *
     * @param   string  $value      The value to use in the SQL statement
     * @return  string              The safely quoted value
     * @access  public
     */
    public function quote($value)
    {
        if (is_int($value)) {
            return $value;
        } elseif (is_bool($value)) {
            return $value ? '1' : '0';
        } elseif (is_null($value)) {
            return 'NULL';
        } elseif (is_float($value)) {
            $value = str_replace(',', '.', strval(floatval($value)));
        }

        return "'" . $this->escape($value) . "'";
    }

    /**
     * Surrounds the given value using {@link http://dev.mysql.com/doc/refman/5.0/en/identifier-qualifiers.html identifier}
     * syntax. Should be used for database/table/column names when unknown
     * values or reserved words are used.
     *
     * <code>
     * $col_name = $db->quoteIdentifier('table.column');
     * </code>
     *
     * @param   string  $value      The identifier name
     * @return  string              The safely quoted value
     * @access  public
     */
    public function quoteIdentifier($value)
    {
        $names = explode('.', $value);
        foreach ($names as &$name) {
            $name = '`' . str_replace('`', '``', $value) . '`';
        }
        return implode('.', $names);
    }

    /**
     * Escapes a value for safe use in an SQL statement. This function will try
     * to establish a connection to the database if it has not already been
     * established in order to use the mysql_real_escape_string() function. This
     * has the advantage of taking into account the current character set.
     *
     * <code>
     * $name = $db->escape($_GET['name']);
     * $affected_rows = $db->exec("INSERT INTO tbl (name) VALUES ('$name')");
     * </code>
     *
     * @param   string  $value      The value to use in the SQL statement
     * @return  string              The properly escaped value
     * @access  public
     */
    public function escape($value)
    {
        if (!$this->handle) {
            $this->connect();
        }
        return mysql_real_escape_string($value, $this->handle);
    }

    /**
     * Gets a list of all databases on the server.
     *
     * @return  array               The database names
     * @access  public
     */
    public function listDatabases()
    {
        return $this->queryCol('SHOW DATABASES', null, 0);
    }

    /**
     * Gets a list of all the tables in the given database. If no database name
     * is given, will use the current database.
     *
     * @param   string  $db         (optional) The name of the database
     * @return  array               The table names
     * @access  public
     */
    public function listTables($db = null)
    {
        $query = 'SHOW TABLES';
        if ($db != null) {
            $query .= ' FROM ' . $this->escape($db);
        }

        return $this->queryCol($query, null, 0);
    }

    /**
     * Gets a list of all the fields in the given table.
     *
     * @param   string  $table      The name of the table
     * @return  array               The field names
     * @access  public
     */
    public function listFields($table)
    {
        $query = 'SHOW COLUMNS FROM ' . $this->escape($table);
        return $this->queryCol($query, null, 0);
    }

    /**
     * Gets the number of rows that were affected by the last query.
     *
     * @return  int         The number of affected rows
     * @access  public
     */
    public function affectedRows()
    {
        return @mysql_affected_rows($this->handle);
    }

    /**
     * Gets the ID generated for an AUTO_INCREMENT column by the last INSERT
     * query.
     *
     * @return  string      The last insert ID
     * @access  public
     */
    public function insertId()
    {
        return @mysql_insert_id($this->handle);
    }

    /**
     * Gets the number of rows from this connection's current result set. Only
     * valid for statements that return a result set (i.e. SELECT, SHOW, etc.).
     *
     * @return  int         The number of rows in the result set
     * @access  public
     */
    public function numRows()
    {
        return @mysql_num_rows($this->result);
    }

    /**
     * Gets the number of fields in this connection's current result set. Only
     * valid for statements that return a result set (i.e. SELECT, SHOW, etc.).
     *
     * @return  int         The number of fields in the result set
     * @access  public
     */
    public function numFields()
    {
        return @mysql_num_fields($this->result);
    }

    /**
     * Seeks to the given record in the result set, moving the internal result
     * pointer.
     *
     * @param   int         $num        The result number to seek to
     * @return  bool                    True on success, false on failure
     * @access  public
     */
    public function seek($num)
    {
        return @mysql_data_seek($this->result, $num);
    }

    /**
     * Fetches the next result from this connection's result set as an array
     * and advances the result set pointer. The type of array to be returned is
     * determined by the $mode parameter. Defaults to an associative array.
     *
     * @param   int         $mode       (optional) The fetch mode to use, one of
     *                                  MYSQL_ASSOC, MYSQL_NUM, or MYSQL_BOTH
     * @param   int         $num        (optional) The result number to fetch
     * @return  array                   The next result as an array
     * @access  public
     */
    public function fetchRow($mode = MYSQL_ASSOC, $num = null)
    {
        if ($num != null && $this->seek($num) === false) {
            return null;
        }

        $row = @mysql_fetch_array($this->result, $mode);
        if (!$row && $this->options['auto_free']) {
            $this->free();
        }

        return $row;
    }

    /**
     * Fetches the next result from this connection's result set as an object
     * using the given class and constructor parameters, and advances the result
     * set pointer. Uses mysql_fetch_object().
     *
     * @param   string      $cls        (optional) The name of the class to
     *                                  create instances of
     * @param   array       $args       (optional) Arguments to pass to the
     *                                  class constructor
     * @param   int         $num        (optional) The result number to fetch
     * @return  object                  The next result as an object
     * @access  public
     */
    public function fetchObj($cls = 'stdClass', $args = array(), $num = null)
    {
        if ($num != null && $this->seek($num) === false) {
            return null;
        }

        if ($cls == 'stdClass' || empty($args)) {
            // stdClass does not have a constructor, will not accept constructor
            // arguments
            $obj = @mysql_fetch_object($this->result, $cls);
        } else {
            $obj = @mysql_fetch_object($this->result, $cls, $args);
        }
        if (!$obj && $this->options['auto_free']) {
            $this->free();
        }

        return $obj;
    }

    /**
     * Frees this connection's result set resource.
     *
     * @return  bool                True on success, false on failure
     * @access  public
     */
    public function free()
    {
        $ret = is_resource($this->result) ? mysql_free_result($this->result) : false;
        $this->result = null;
        return $ret;
    }

    /**
     * Returns the error number returned by the last MySQL function call.
     *
     * @return  int         The error number
     * @access  public
     */
    public function errorCode()
    {
        return @mysql_errno($this->handle);
    }

    /**
     * Returns the error message from the last MySQL function call.
     *
     * @return  string      The error message
     * @access  public
     */
    public function error()
    {
        return @mysql_error($this->handle);
    }

    /**
     * Logs the given debug message along with the current timestamp.
     *
     * @param   string  $msg        The debug message
     * @return  void
     * @access  public
     */
    public function debug($msg)
    {
        $this->debug_info[] = date('c') . ": $msg";
    }

    /**
     * Gets debugging information associated with this connection. May be
     * returned in an array or string format.
     *
     * @param   bool    $array      True to return the debug info in an array
     * @return  mixed               Debugging information
     * @access  public
     */
    public function getDebugInfo($array = false)
    {
        return $array ? $this->debug_info : implode("\n", $this->debug_info);
    }

    /**
     * Sets the {@link $options option} with the given name to the supplied
     * value. Available options include the following:
     *
     * - persistent             Set this true to use persistent connections.
     *                          Defaults to true.
     * - count_delete           Set this true to automatically modify DELETE
     *                          statements to return the number of rows deleted.
     *                          Statements are only modified when no WHERE
     *                          condition is present. Defaults to true.
     * - auto_free              Set this false to disable automatically freeing
     *                          result sets when the result set is exhausted
     *                          using fetchRow() or fetchObj(). Defaults to
     *                          true.
     * - quote_identifiers      Set this false to disable automatically quoting
     *                          identifier names in select(), insert(), update()
     *                          and delete(). Defaults to true.
     *
     * @param   string  $option     The name of the option to set
     * @param   mixed   $value      The value of the option
     * @return  void
     * @access  public
     */
    public function setOption($option, $value)
    {
        if (array_key_exists($option, $this->options)) {
            $this->options[$option] = $value;
        }
    }

    /**
     * Gets the option with the given name.
     *
     * @param   string  $option     The name of the option to get
     * @return  mixed               The value of the option
     * @access  public
     */
    public function getOption($option)
    {
        return isset($this->options[$option]) ? $this->options[$option] : null;
    }

    /**
     * Compiles and executes a basic SELECT statement on the given table using
     * the given fields and WHERE clause. The statement will be of the general
     * form:
     *
     * SELECT fields FROM tbl WHERE condition
     *
     * The fields parameter may be given as a string or an array:
     *
     * <code>
     * $fields = array('id', 'name');
     * $result = $db->select($fields, 'users', 'id=1');
     * </code>
     *
     * @param   mixed       $fields     The name(s) of the field(s) to SELECT
     * @param   string      $tbl        The name of the table to SELECT from
     * @param   string      $where      (optional) The WHERE clause
     * @return  resource                The result of the query
     * @access  public
     */
    public function select($fields, $tbl, $where = null)
    {
        if (!is_array($fields)) {
            $fields = explode(',', $fields);
        }
        foreach ($fields as &$field) {
            $field = trim($field);
            if ($this->options['quote_identifiers']) {
                $field = $this->quoteIdentifier($field);
            }
        }
        $fields = implode(',', $fields);

        if ($this->options['quote_identifiers']) {
            $tbl = $this->quoteIdentifier($tbl);
        }

        $sql = "SELECT $fields FROM $tbl";
        if ($where) {
            $sql .= ' WHERE ' . $where;
        }

        return $this->query($sql);
    }

    /**
     * Compiles and executes a basic INSERT statement on the given table using
     * the given data. The data provided should be an associative array of
     * field => value pairs that correspond to field names in the database. The
     * statement will be of the general form:
     *
     * INSERT INTO tbl (keys) VALUES (values)
     *
     * @param   string      $tbl        The name of the table to INSERT into
     * @param   array       $data       The data to insert
     * @return  int                     The number of affected rows
     * @access  public
     */
    public function insert($tbl, $data)
    {
        $keys = array();
        $values = array();
        foreach ($data as $key => $value) {
            if ($this->options['quote_identifiers']) {
                $key = $this->quoteIdentifier($key);
            }
            $keys[] = $key;
            $values[] = $this->quote($value);
        }
        $keys = implode(',', $keys);
        $values = implode(',', $values);

        if ($this->options['quote_identifiers']) {
            $tbl = $this->quoteIdentifier($tbl);
        }

        $sql = "INSERT INTO $tbl ($keys) VALUES ($values)";

        return $this->exec($sql);
    }

    /**
     * Compiles and executes a basic UPDATE statement on the given table using
     * the given data. The data provided should be an associative array of
     * field => value pairs that correspond to field names in the database. The
     * statement will be of the general form:
     *
     * UPDATE tbl SET key=value WHERE condition
     *
     * IMPORTANT: If the WHERE clause is not provided, every record in the table
     * will be updated!
     *
     * @param   string      $tbl        The name of the table to UPDATE
     * @param   array       $data       The data to update
     * @param   string      $where      (optional) The WHERE clause
     * @return  int                     The number of affected rows
     * @access  public
     */
    public function update($tbl, $data, $where = null)
    {
        $set = array();
        foreach ($data as $key => $value) {
            if ($this->options['quote_identifiers']) {
                $key = $this->quoteIdentifier($key);
            }
            $set[] = $key . '=' . $this->quote($value);
        }

        if ($this->options['quote_identifiers']) {
            $tbl = $this->quoteIdentifier($tbl);
        }

        $sql = "UPDATE $tbl SET " . implode(',', $set);
        if ($where) {
            $sql .= ' WHERE ' . $where;
        }

        return $this->exec($sql);
    }

    /**
     * Compiles and executes a basic DELETE statement on the given table. The
     * statement will be of the general form:
     *
     * DELETE FROM tbl WHERE condition
     *
     * IMPORTANT: If the WHERE clause is not provided, every record in the table
     * will be deleted!
     *
     * @param   string      $tbl        The name of the table to DELETE from
     * @param   string      $where      (optional) The WHERE clause
     * @return  int                     The number of affected rows
     * @access  public
     */
    public function delete($tbl, $where = null)
    {
        if ($this->options['quote_identifiers']) {
            $tbl = $this->quoteIdentifier($tbl);
        }

        $sql = "DELETE FROM $tbl";
        if ($where) {
            $sql .= ' WHERE ' . $where;
        }

        return $this->exec($sql);
    }

    /**
     * Tell whether a query is a data manipulation or data definition query
     *
     * Examples of data manipulation queries are INSERT, UPDATE and DELETE.
     * Examples of data definition queries are CREATE, DROP, ALTER, GRANT,
     * REVOKE.
     *
     * @param   string  $query      The SQL query
     * @return  bool                True if the query is manipulative, false
     *                              otherwise
     * @access  public
     * @static
     */
    public static function isManip($query)
    {
        $manips = implode('|', array(
            'INSERT',
            'UPDATE',
            'DELETE',
            'REPLACE',
            'CREATE',
            'DROP',
            'LOAD DATA',
            'SELECT .* INTO .* FROM',
            'COPY',
            'ALTER',
            'GRANT',
            'REVOKE',
            'LOCK',
            'UNLOCK'
        ));

        return (bool) preg_match('/^\s*"?(' . $manips . ')\s+/i', $query);
    }

}

/**
 * A prepared statement emulation class. A prepared statement is a query that
 * can be run multiple times. Each time it is run, the placeholders it contains,
 * if any, will be replaced by the contents of DB::execute()'s $data argument.
 *
 * Three types of placeholders can be used:
 *
 * ?    Scalar value (i.e. strings, integers). Data will automaticaly be escaped
 *      and quoted.
 * &    Binary data, requires a file name. The file's contents are inserted into
 *      the query (e.g. saving binary data).
 * !    Data will be inserted into the query "as is".
 *
 * Use backslashes to escape placeholder characters if you don't want them to
 * be interpreted as placeholders.
 *
 * <code>
 * $stmt = new DB_Statement("UPDATE tbl SET col=? WHERE col='up \& down'");
 * </code>
 *
 * May be used to create standalone statements that are not tied to a particular
 * database connection handle:
 *
 * <code>
 * $params = array(
 *     "Michael's text",
 *     "'it''s good'",
 *     'filename.txt'
 * );
 * $stmt = new DB_Statement('INSERT INTO tbl (a, b, c) VALUES (?, !, &)');
 * $result = $db->execute($stmt, $params); // use arbitrary handle
 * </code>
 *
 * or to create handle-specific statements:
 *
 * <code>
 * $stmt = $db->prepare('INSERT INTO tbl (a, b, c) VALUES (?, !, &)');
 * $result = $stmt->execute($params);
 * </code>
 *
 * @package     DB
 * @author      Michael J. I. Jackson <mjijackson@gmail.com>
 * @see         DB::prepare(), DB::execute()
 */
class DB_Statement
{

    /**#@+
     * Data type constants used to record the type of statement placeholders.
     *
     * @var     int
     * @access  public
     */
    const PARAM_SCALAR = 0;
    const PARAM_FILE = 1;
    const PARAM_MISC = 2;
    /**#@-*/

    /**
     * Contains the parsed pieces of this statement split on the placeholders.
     *
     * @var     array
     * @access  private
     */
    private $tokens;

    /**
     * Contains data types of the variables in the statement.
     *
     * @var     array
     * @access  private
     */
    private $types;

    /**
     * Keeps track of whether or not this query is manipulative. Determined up
     * front to save time later.
     *
     * @var     bool
     * @access  private
     */
    private $is_manip;

    /**
     * The database connection handle for this statement. May be set in
     * __construct() but is not necessary if passed directly to DB::execute().
     *
     * @var     DB
     * @access  private
     */
    private $db;

    /**
     * Constructor.
     *
     * @param   string      $query      The query statement to prepare
     * @param   DB          $db         (optional) The database connection
     *                                  handle to use for this statement
     * @access  public
     */
    public function __construct($query, $db = null)
    {
        $tokens = preg_split('/((?<!\\\)[&?!])/', $query, -1, PREG_SPLIT_DELIM_CAPTURE);

        foreach ($tokens as $token) {
            switch ($token) {
                case '?':
                    $this->types[] = self::PARAM_SCALAR;
                    break;
                case '&':
                    $this->types[] = self::PARAM_FILE;
                    break;
                case '!':
                    $this->types[] = self::PARAM_MISC;
                    break;
                default:
                    $this->tokens[] = preg_replace('/\\\([&?!])/', "\\1", $token);
            }
        }

        $this->is_manip = DB::isManip($query);

        if ($db) {
            $this->db = $db;
        }
    }

    /**
     * Compiles and executes the given prepared statement using the given
     * parameter(s). The parameter(s) may be given as a scalar (if there is only
     * one placeholder in the statement) or an array. The database connection
     * handle must be provided here unless it was already given when this
     * statement was {@link __construct() created}.
     *
     * @param   mixed           $params     The parameters to use
     * @param   DB              $db         (optional) The database connection
     *                                      to use to execute the statement
     * @return  mixed                       The number of affected rows if the
     *                                      query is {@link DB::isManip() manipulative},
     *                                      the query result otherwise
     * @throws  DB_NoHandleException        If no connection handle is provided
     *                                      and none was specified when this
     *                                      statement was created
     * @throws  DB_MismatchException        If the number of parameters does not
     *                                      match the number of placeholders
     * @throws  DB_FileAccessException      If one of the parameters is a file
     *                                      and there is an error reading the
     *                                      file
     * @access  public
     */
    public function execute($params = array(), $db = null)
    {
        if (!$db) {
            $db = $this->db;
            if (!$db) {
                throw new DB_NoHandleException();
            }
        }

        $params = (array) $params;

        if (count($this->types) != count($params)) {
            throw new DB_MismatchException(implode(' ', $this->tokens), $params);
        }

        $query = $this->tokens[0];

        $i = 0;
        foreach ($params as $param) {
            if ($this->types[$i] == self::PARAM_SCALAR) {
                $query .= $db->quote($param);
            } elseif ($this->types[$i] == self::PARAM_FILE) {
                $fp = @fopen($param, 'rb');
                if (!$fp) {
                    throw new DB_FileAccessException($param);
                }
                $query .= $db->quote(fread($fp, filesize($param)));
                fclose($fp);
            } else {
                $query .= $param;
            }

            $query .= $this->tokens[++$i];
        }

        return $this->is_manip ? $db->exec($query) : $db->query($query);
    }

}

/**
 * The base class for all database exceptions.
 *
 * @package     DB
 * @author      Michael J. I. Jackson <mjijackson@gmail.com>
 */
class DB_Exception extends Majic_Exception {}

/**
 * Connect exception. Thrown when the database connection cannot be established
 * using the given criteria.
 *
 * @package     DB
 * @author      Michael J. I. Jackson <mjijackson@gmail.com>
 */
class DB_ConnectException extends DB_Exception
{

    public function __construct($host, $user, $pass)
    {
        parent::__construct("Unable to connect to database");
    }

}

/**
 * Database select exception. Thrown when the given database cannot be
 * selected using mysql_select_db().
 *
 * @package     DB
 * @author      Michael J. I. Jackson <mjijackson@gmail.com>
 */
class DB_SelectDatabaseException extends DB_Exception
{

    /**
     * Constructor.
     *
     * @param   string  $db     The name of the database
     * @access  public
     */
    public function __construct($db)
    {
        parent::__construct("Unable to select database \"$db\"");
    }

}

/**
 * Query exception. Thrown when the given query throws an error using
 * mysql_query().
 *
 * @package     DB
 * @author      Michael J. I. Jackson <mjijackson@gmail.com>
 */
class DB_QueryException extends DB_Exception
{

    /**
     * Constructor.
     *
     * @param   string  $sql    The SQL that was used
     * @access  public
     */
    public function __construct($sql, $errno, $error)
    {
        parent::__construct("Query error ($errno) \"$error\" in SQL statement \"$sql'\"");
    }

}

/**
 * No handle exception. Thrown when a prepared statement does not have a
 * database handle to execute with.
 *
 * @package     DB
 * @author      Michael J. I. Jackson <mjijackson@gmail.com>
 */
class DB_NoHandleException extends DB_Exception
{

    /**
     * Constructor.
     *
     * @access  public
     */
    public function __construct()
    {
        parent::__construct("No database connection handle provided for prepared statement");
    }

}

/**
 * Mismatch exception. Thrown when the number of parameters passed to a prepared
 * statement does not match the number of placeholders.
 *
 * @package     DB
 * @author      Michael J. I. Jackson <mjijackson@gmail.com>
 */
class DB_MismatchException extends DB_Exception
{

    /**
     * Constructor.
     *
     * @param   string      $sql        The SQL (without placeholders)
     * @param   array       $data       The parameters that were passed
     * @access  public
     */
    public function __construct($sql, $data)
    {
        parent::__construct("Incorrect number of parameters supplied for SQL statement \"$sql\"");
    }

}

/**
 * File access exception. Thrown when a file cannot be accessed with read
 * permissions in a prepared statement where one of the parameters is a file.
 *
 * @package     DB
 * @author      Michael J. I. Jackson <mjijackson@gmail.com>
 */
class DB_FileAccessException extends DB_Exception
{

    /**
     * Constructor.
     *
     * @param   string      $file       The name of the file
     * @access  public
     */
    public function __construct($file)
    {
        parent::__construct("Unable to read from file \"$file\"");
    }

}

/**
 * Invalid lock exception. Thrown when an invalid lock type is used.
 *
 * @package     DB
 * @author      Michael J. I. Jackson <mjijackson@gmail.com>
 */
class DB_InvalidLockException extends DB_Exception
{

    /**
     * Constructor.
     *
     * @param   string      $lock       The name of the lock type that was used
     * @access  public
     */
    public function __construct($lock)
    {
        parent::__construct("Invalid lock \"$lock\"");
    }

}

?>
